#!/usr/bin/env python
# -*- coding:utf-8 -*-

# file:do_exam1.py
# author:王雯雯
# datetime:2024-11-12 
# software: PyCharm

"""
    将题目写在这儿：题目1 定义字符串并赋值，打印输出以下格式的内容；
"""
import pymysql
import DBUtils.PooledDB
from..config.database_config import config
from..models.class_model import Class


# 创建数据库连接池
pool = PooledDB(
    creator=pymysql,
    **config,
    maxconnections=10,
    mincached=2,
    blocking=True
)


# 根据班级编号查询班级信息
def query_class_by_id(classid):
    try:
        connection = pool.connection()
        cursor = connection.cursor()
        sql = "SELECT * FROM class WHERE ClassID = %s"
        cursor.execute(sql, (classid,))
        result = cursor.fetchone()
        if result:
            class_obj = Class(*result)
            cursor.close()
            return class_obj
        connection.close()
    except pymysql.Error as e:
        print(f"查询班级信息时出错: {e}")
    return None


# 插入班级信息
def insert_class(class_obj):
    try:
        connection = pool.connection()
        cursor = connection.cursor()
        sql = "INSERT INTO class (ClassID, ClassName) VALUES (%s, %s)"
        cursor.execute(sql, (class_obj.classid, class_obj.classname))
        connection.commit()
        cursor.close()
        connection.close()
        print("班级信息插入成功")
    except pymysql.Error as e:
        print(f"插入班级信息时出错: {e}")


# 根据班级编号删除班级信息
def delete_class_by_id(classid):
    try:
        connection = pool.connection()
        cursor = connection.cursor()
        sql = "DELETE FROM class WHERE ClassID = %s"
        cursor.execute(sql, (classid,))
        connection.commit()
        cursor.close()
        connection.close()
        print("班级信息删除成功")
    except pymysql.Error as e:
        print(f"删除班级信息时出错: {e}")


# 根据班级名称查询班级信息（模糊查询）
def query_classes_by_name(classname):
    try:
        connection = pool.connection()
        cursor = connection.cursor()
        sql = "SELECT * FROM class WHERE ClassName LIKE %s"
        cursor.execute(sql, (f"%{classname}%",))
        results = cursor.fetchall()
        classes = []
        for result in results:
            class_obj = Class(*result)
            classes.append(class_obj)
        cursor.close()
        connection.close()
        return classes
    except pymysql.Error as e:
        print(f"查询班级信息时出错: {e}")
    return []